package controller;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.KhachHang;
import model.MatHang;
import model.NhaCC;

public class MatHangController {

	public static MatHang getMatHang(int id) {
		Connection conn = DBConection.MYSQL;
		if (conn == null)
			return null;
		try {

			Statement stmt = conn.createStatement();
			String sql = "SELECT * FROM mathang WHERE id=" + id + ";";
			ResultSet rs = stmt.executeQuery(sql);
			MatHang mh;
			if (rs.next()) {
				mh = new MatHang();
				mh.setMaMH(id);
				mh.setIdNCC(rs.getInt("idncc"));
				mh.setTenMH(rs.getString("tenmh"));
				mh.setGia(rs.getDouble("gia"));
				mh.setGiaNhap(rs.getDouble("giaNhap"));
				mh.setSoLuongMH(rs.getInt("soluong"));

				return mh;
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();

			return null;
		}
	}

	public static List<MatHang> getDSMatHang() {
		Connection conn = DBConection.MYSQL;
		if (conn == null)
			return null;
		try {

			Statement stmt = conn.createStatement();
			String sql = "SELECT * FROM mathang";
			ResultSet rs = stmt.executeQuery(sql);
			List<MatHang> list = new ArrayList<>();
			MatHang mh;
			while (rs.next()) {
				mh = new MatHang();
				mh.setMaMH(rs.getInt("id"));
				mh.setIdNCC(rs.getInt("idncc"));
				mh.setTenMH(rs.getString("tenmh"));
				mh.setGia(rs.getDouble("gia"));
				mh.setGiaNhap(rs.getDouble("giaNhap"));
				mh.setSoLuongMH(rs.getInt("soluong"));
				list.add(mh);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();

			return null;
		}
	}

	public static void insertMatHang(MatHang mh) {

		try {
			Statement sta = DBConection.MYSQL.createStatement();
			String query = "INSERT INTO mathang (id,idncc,tenmh,gia,giaNhap,soluong) VALUES (";
			query += "'" + mh.getMaMH() + "',";
			query += "'" + mh.getIdNCC() + "',";
			query += "'" + mh.getTenMH() + "',";
			query += "'" + mh.getGia() + "',";
			query += "'" + mh.getGiaNhap() + "',";
			query += "'" + mh.getSoLuongMH() + "')";
			System.out.println(query);
			sta.execute(query);
			return;

		} catch (Exception e) {

			e.printStackTrace();
		}
	}

	public static void updateMatHang(MatHang mh) {
		try {
			Statement st = DBConection.MYSQL.createStatement();
			String query = "UPDATE mathang SET " + "id=" + "'" + mh.getMaMH()
					+ "'" + "," + "idncc= " + "'" + mh.getIdNCC()
					+ "'" + "," + "tenmh=" + "'" + mh.getTenMH() + "'" + ","
					+ "gia= " + "'" + mh.getGia() + "'" + "," + "giaNhap= "
					+ "'" + mh.getGiaNhap() + "'" + "," + "soluong=" + "'"
					+ mh.getSoLuongMH() + "'" + " WHERE id =" + mh.getMaMH();
			System.out.println(query);
			st.execute(query);
			return;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void deleteMatHang(int id) {
		try {
			Statement st = DBConection.MYSQL.createStatement();
			String query = "DELETE FROM mathang WHERE id = "+ id;
			st.execute(query);
			return;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	public static List<MatHang> getMatHang1(String name){
		Connection conn= DBConection.MYSQL;
		if(conn==null)
			return null;
		try{
			 Statement stmt = conn.createStatement();
	            String sql = "SELECT * FROM mathang WHERE tenmh LIKE '%" + name + "%'";
//	            sql+=" OR diachi LIKE '%"+name+"%'";
	            ResultSet rs = stmt.executeQuery(sql);
	           List<MatHang> list = new ArrayList<>();
	            while(rs.next()) {
	             MatHang m = new MatHang();
	              m.setMaMH(rs.getInt("id"));
	              m.setIdNCC(rs.getInt("idncc"));
	              m.setTenMH(rs.getString("tenmh"));
	              m.setGia(rs.getDouble("gia"));
	              m.setGiaNhap(rs.getDouble("giaNhap"));
	              m.setSoLuongMH(rs.getInt("soluong"));
	              list.add(m);
	            }
	            return list;
	        } catch (Exception e) {
	            e.printStackTrace();

	            return null;
	        }
	    }


	public static void main(String[] args) {
		MatHangController m = new MatHangController();
		System.out.println(m.getDSMatHang());
		m.updateMatHang(new MatHang(5, 1, "Nồi cơm điện", 2500000, 2300000, 10));
	}
}
